﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;
using AMS.Inventory.PCClient.InventoryHelpClass;
using System.Text;

namespace AMS.Inventory.PCClient
{
    public partial class InventoryPlanFrm : Form
    {
        public InventoryPlanFrm()
        {
            InitializeComponent();
        }

        private const string PDASdfFilePath = "\\Program Files\\ams.inventory.pda\\PDA_AMS_Inventory.db3";
        private string PCSdfFilePath = Application.StartupPath + @"\PDA_AMS_Inventory.db3";
        private int MaxNum;
        public string m_UserCode = "";
        private SqlDb m_Db = new SqlDb();
        SqlSdfDb m_SqlSdfDb = new SqlSdfDb();
        private string m_PlanId = "";
        List<string> m_PlanList = new List<string>();
        List<string> m_SqlList = new List<string>();
        List<string> updateSqlList = new List<string>();
        private bool IsDataUp = false;
        private bool IsDataUpdate = false;
        int factRow;

        public InventoryPlanFrm(string strUserCode)
        {
            m_UserCode = strUserCode;
            InitializeComponent();
        }


        private void InventoryPlanFrm_Load(object sender, EventArgs e)
        {


            dateTimePickerStartDate.Text = DateTime.Now.AddMonths(-1).ToShortDateString();
            dateTimePickerEndDate.Text = DateTime.Now.ToShortDateString();
            var f = new LoginFrm();
            f.ShowDialog();
            if (f.DialogResult == DialogResult.Cancel)
                Application.Exit();

        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            try
            {
                QueryData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }


        }

        private void QueryData()
        {
            var strName = txtName.Text.Trim();
            var strStartDate = dateTimePickerStartDate.Value.ToString("yyyy-MM-dd");
            var strFinishDate = dateTimePickerEndDate.Value.ToString("yyyy-MM-dd");

            var p = new SqlParameter("Name", strName);
            var p2 = new SqlParameter("StartDate", strStartDate);
            var p3 = new SqlParameter("FinishDate", strFinishDate);
            var p4 = new SqlParameter("Code", LoginUser.UserCode);

            var dt = m_Db.GetTableRunProc("InventoryPlanSelect", new[] { p, p2, p3, p4 });
            dataGridView1.DataSource = dt;
        }



        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (checkBox1.Checked)
            {
                for (var i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    dataGridView1.Rows[i].Selected = true;
                }
            }
            else
            {
                for (var i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    dataGridView1.Rows[i].Selected = false;
                }
            }

        }

        #region 數據下載
        private void btnDownLoadData_Click(object sender, EventArgs e)
        {

            IsDataUp = false;
            IsDataUpdate = false;
            m_SqlList.Clear();
            if (dataGridView1.CurrentRow == null || dataGridView1.SelectedRows.Count == 0)
            {
                MessageBox.Show("請選擇盤點計劃單", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //檢查C 盤SDF 文件是否存在
            var bSDFExists = false;
            if (File.Exists(PCSdfFilePath))
            {
                bSDFExists = true;
            }

            //存在就刪掉
            if (bSDFExists)
            {
                try
                {
                    File.Delete(PCSdfFilePath);
                }
                catch
                {
                    MessageBox.Show("未清除歷史數據文件，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }

            //初始化PDA 文件操作類
            var pdaFileControl = new PDAFileControl();

            //連接到 PDA
            var bPDAConnect = pdaFileControl.ConnectToPDA();

            if (bPDAConnect == false)
            {
                MessageBox.Show("請確認 PDA 已經跟電腦進行同步連接 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //檢查PDA中是否存在SDF 文件 
            var bPDAExits = pdaFileControl.PDAFileExists(PDASdfFilePath);

            if (bPDAExits)
            {
                //從PDA 拷貝SDF文件到C盤
                var bPDACopy = pdaFileControl.CopyFileFromPDA(PDASdfFilePath, PCSdfFilePath);

                if (bPDACopy == false)
                {
                    MessageBox.Show("未創建臨時數據文件，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    pdaFileControl.DisConnectToPDA();
                    return;
                }

            }
            else
            {
                MessageBox.Show("未能在PDA中找到數據庫文件,請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                pdaFileControl.DisConnectToPDA();
                return;
            }

            MaxNum = 0;
            foreach (DataGridViewRow dtGvRow in dataGridView1.SelectedRows)
            {
                var Row = ((DataRowView)dtGvRow.DataBoundItem).Row;
                m_PlanId = Row["Id"].ToString();
             
                    int access = 0;
                    access = m_SqlSdfDb.ExecNoneQuery("delete from AmsInventory_InventoryInfo where PlanId='" + m_PlanId + "'");
                    access = m_SqlSdfDb.ExecNoneQuery("delete from AmsInventory_PlanInfo where Id='" + m_PlanId + "'");

                    if (access < 0)
                    {
                        MessageBox.Show("盤點計劃：(" + Row["計劃名稱"] + ")已經在PDA中下載了刪除時出錯！", "", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }
               

                var p = new SqlParameter("PlanId", m_PlanId);
                var dt = m_Db.GetTableRunProc("PlanDataDownLoadByPlanId", new[] { p });
                if (dt != null && dt.Rows.Count > 0)
                {
                    const string strInsertSql1 = "INSERT INTO [AmsInventory_PlanInfo]([ID],[Name],[CreateDate] ,[StartDate],[FinishDate] ,[Memo],[State] ,[ResponsibleId]) VALUES ";

                    foreach (DataRow row in dt.Rows)
                    {
                        var strTempSql = strInsertSql1 + "('" + row["ID"] + "','" + row["Name"] + "','" + row["CreateDate"] + "','" +
                                            row["StartDate"] + "','" + row["FinishDate"] + "','" + row["Memo"] + "','" +
                                            row["State"] + "','" + row["ResponsibleId"] + "');";
                        m_SqlList.Add(strTempSql);


                    }
                }
                else
                {
                    MessageBox.Show("當前沒有滿足盤點計劃的資產！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                var p2 = new SqlParameter("PlanId", m_PlanId);
                var dt2 = m_Db.GetTableRunProc("InventoryInfoByPlanId", new[] { p2 });
                if (dt2 != null && dt2.Rows.Count > 0)
                {
                    const string strInsertSql2 = "INSERT INTO [AmsInventory_InventoryInfo]([Id],[BaseObjectId] ,[PlanId] ,[InventoryDate],[CommintDate] ,[InventoryState]" +
                                                ",[InventoryMemo],[OldBarcode],[InventoryUserInfo] ,[AssetTypeInfo] ,[TypeName],[Barcode],[CheckedDate]" +
                                                ",[DepartmentInfo],[Location] ,[Manufacturers] ,[Memo] ,[ModelNumber] ,[Name],[NatureInfo] ,[ParentBarcode]" +
                                                ",[SerialNumber]  ,[ShelfLife],[SiteInfo],[SourceInfo],[StartUpDate] ,[StateInfo] ,[Supplier]  ,[UnitInfo]" +
                                                " ,[UseYears],[InitDate] ,[UsersInfo] ,[SizeInfo]) VALUES ";


                    foreach (DataRow row in dt2.Rows)
                    {
                        var strTempSql = strInsertSql2 + "('" + row["Id"] + "','" + row["BaseObjectId"] + "','" + row["PlanId"] + "','" +
                                            row["InventoryDate"] + "','"
                                            + row["CommintDate"] + "','" + row["InventoryState"] + "','" +
                                            row["InventoryMemo"] + "','" + row["OldBarcode"] + "','"
                                            + row["InventoryUserInfo"] + "','" + row["AssetTypeInfo"] + "','" +
                                            row["TypeName"] + "','" + row["Barcode"] + "','"
                                            + row["CheckedDate"] + "','" + row["DepartmentInfo"] + "','" +
                                            row["Location"] + "','" + row["Manufacturers"] + "','"
                                            + row["Memo"] + "','" + row["ModelNumber"] + "','" + row["Name"] + "','" + row["NatureInfo"] +
                                            "','" + row["ParentBarcode"] + "','"
                                            + row["SerialNumber"] + "','" + row["ShelfLife"] + "','" + row["SiteInfo"] +
                                            "','" + row["SourceInfo"] + "','"
                                            + row["StartUpDate"] + "','" + row["StateInfo"] + "','" + row["Supplier"] +
                                            "','" + row["UnitInfo"] + "','"
                                            + row["UseYears"] + "','" + row["InitDate"] + "','" + row["UsersInfo"] +
                                            "','" + row["SizeInfo"] + "');";
                        m_SqlList.Add(strTempSql);


                    }
                }
                MaxNum += dt.Rows.Count;
                if (dt2 != null) MaxNum += dt2.Rows.Count;
                var BaseDt = m_SqlSdfDb.GetDataTable("select * from AmsInventory_BaseDataInfo");
                if (BaseDt != null && BaseDt.Rows.Count > 0)
                {
                }
                else
                {
                    var p3 = new SqlParameter("PlanId", m_PlanId);
                    var dt3 = m_Db.GetTableRunProc("[BaseDataInfoSelect]", new[] { p3 });


                    if (dt3 != null) MaxNum += dt3.Rows.Count;

                    if (dt3 != null && dt3.Rows.Count > 0)
                    {
                        const string strInsertSql3 =
                            "INSERT INTO  [AmsInventory_BaseDataInfo] ([KeyVal] ,[KeyVal2],[KeyVal3] ,[ClassName] ,[TypeName]) VALUES ";

                        foreach (DataRow row in dt3.Rows)
                        {
                            var strTempSql = strInsertSql3 + "('" + row["KeyVal"] + "','" + row["KeyVal2"] + "','" +
                                              row["KeyVal3"] + "','"
                                              + row["ClassName"] + "','" + row["TypeName"] + "');";

                            m_SqlList.Add(strTempSql);

                        }
                    }
                }
                var UserDt = m_SqlSdfDb.GetDataTable("select [Code] ,[Password],[Name] ,[Number] ,[Email],[Mobile],[IsInUsed] from Frame_User");
                if (UserDt != null)
                {
                    if (UserDt.Rows.Count == 0)
                    {
                        var pcUserDt = m_Db.GetDataSet("select * from Frame_User");
                        if (pcUserDt != null && pcUserDt.Tables[0].Rows.Count > 0)
                        {
                            MaxNum += pcUserDt.Tables[0].Rows.Count;
                            const string strInsertSql3 =
                           "INSERT INTO  [Frame_User] ([Code] ,[Password],[Name] ,[Number] ,[Email],[Mobile],[IsInUsed]) VALUES ";

                            foreach (DataRow row in pcUserDt.Tables[0].Rows)
                            {
                                var Password = publ.VitDecryptString(row["Password"].ToString());
                                var strTempSql = strInsertSql3 + "('" + row["Code"] + "','" + Password + "','" +
                                                  row["Name"] + "','" + row["Number"] + "','" + row["Email"] + "','" +
                                                  row["Mobile"] + "','" + row["IsInUsed"] + "');";

                                m_SqlList.Add(strTempSql);

                            }
                        }
                    }
                }

            }

            //將SQL Server 數據庫數據導入C 盤 Sdf 文件操作
            //DownLoadPCDataToPC();

            backgroundWorker1.RunWorkerAsync();//運行backgroundWorker組件
            //顯示進度條窗體
            var form = new ProcessForm(backgroundWorker1, MaxNum);
            form.ShowDialog(this);
            form.Close();


            //檢查C盤文件是否存在
            if (File.Exists(PCSdfFilePath) == false)
            {
                MessageBox.Show("未找到臨時數據文件，未能將數據導入 PDA ，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                pdaFileControl.DisConnectToPDA();
                return;
            }
            //檢查PDA中是否存在SDF 文件 ,存在則刪除, 不用檢查結果,因為拷貝的時候 OverWrite = true
            var MyPDAExits = pdaFileControl.PDAFileExists(PDASdfFilePath);

            if (MyPDAExits)
            {
                pdaFileControl.DeletePDAFile(PDASdfFilePath);
            }


            //從C盤拷貝數據到PDA
            var MyPDACopy = pdaFileControl.CopyFileToPDA(PCSdfFilePath, PDASdfFilePath);

            if (MyPDACopy == false)
            {

                MessageBox.Show("數據下載失敗，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            pdaFileControl.DisConnectToPDA();
            QueryData();

        }

        #endregion



        #region 數據上傳
        private void btnUpLoadData_Click(object sender, EventArgs e)
        {
            IsDataUp = true;
            updateSqlList.Clear();
            factRow = 0;
            bool bSDFExists = false;
            if (File.Exists(PCSdfFilePath))
            {
                bSDFExists = true;
            }

            if (bSDFExists)
            {
                try
                {
                    File.Delete(PCSdfFilePath);

                }
                catch
                {
                    MessageBox.Show("未清除歷史數據文件，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }

            var pdaFileControl = new PDAFileControl();
            var bPDAConnect = pdaFileControl.ConnectToPDA();
            if (bPDAConnect == false)
            {
                MessageBox.Show("請確認 PDA 已經跟電腦進行同步連接 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            var bPDAExits = pdaFileControl.PDAFileExists(PDASdfFilePath);

            if (bPDAExits)
            {

                var bPDACopy = pdaFileControl.CopyFileFromPDA(PDASdfFilePath, PCSdfFilePath);

                if (bPDACopy == false)
                {
                    MessageBox.Show("未創建臨時數據文件，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    pdaFileControl.DisConnectToPDA();
                    return;
                }

            }
            else
            {
                MessageBox.Show("未能在PDA中找到數據文件,請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                pdaFileControl.DisConnectToPDA();
                return;
            }


            //檢查C盤文件是否存在
            if (File.Exists(PCSdfFilePath) == false)
            {
                MessageBox.Show("未找到臨時數據文件，未能將 PDA 數據導入 PC ，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                pdaFileControl.DisConnectToPDA();
                return;
            }


            //將Sdf 數據文件 導入 SQL Server 數據庫操作
            m_PlanList.Clear();

            const string strSelectSql = "SELECT [Id], [BaseObjectId] ,[PlanId],datetime([InventoryDate]) InventoryDate,datetime([CommintDate]) CommintDate," +
                "(case [InventoryState] when 'Wait' then 'Lost' when 'Match' then 'Match' when 'Updated' then 'Updated' when 'Add' then 'Add' when 'Lost' then 'Lost' end) InventoryState ,[InventoryMemo],[OldBarcode]" +
                                      ",[InventoryUserInfo] ,[AssetTypeInfo],[TypeName],[Barcode] ,datetime([CheckedDate]) CheckedDate ,[DepartmentInfo] ,[Location]" +
                                      ",[Manufacturers] ,[Memo] ,[ModelNumber],[Name] ,[NatureInfo],[ParentBarcode],[SerialNumber] ,[ShelfLife]" +
                                      " ,[SiteInfo] ,[SourceInfo],datetime([StartUpDate]) StartUpDate,[StateInfo],[Supplier] ,[UnitInfo],[UseYears],datetime([InitDate]) InitDate,[UsersInfo]" +
                                      ",[SizeInfo] FROM [AmsInventory_InventoryInfo]";

            var dt = m_SqlSdfDb.GetDataTable(strSelectSql);
            if (dt != null) MaxNum = dt.Rows.Count;

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    if (!m_PlanList.Contains(row["PlanId"].ToString()))
                        m_PlanList.Add(row["PlanId"].ToString());
                    var commintDate = DateTime.Now.ToString();
                    string strUpdateSql = "";
                    if (row["InventoryState"].ToString() == "Add")
                    {
                        var ds = m_Db.GetDataSet("select * from AmsInventory_InventoryInfo where Id='" + row["Id"] + "'");
                        if(ds.Tables[0].Rows.Count == 0)
                        {
                            strUpdateSql = "INSERT INTO [AmsInventory_InventoryInfo]([Id],[BaseObjectId] ,[PlanId] ,[InventoryDate],[CommintDate] ,[InventoryState]" +
                                            ",[InventoryMemo],[OldBarcode],[InventoryUserInfo] ,[AssetTypeInfo] ,[TypeName],[Barcode],[CheckedDate]" +
                                            ",[DepartmentInfo],[Location] ,[Manufacturers] ,[Memo] ,[ModelNumber] ,[Name],[NatureInfo] ,[ParentBarcode]" +
                                            ",[SerialNumber]  ,[ShelfLife],[SiteInfo],[SourceInfo],[StartUpDate] ,[StateInfo] ,[Supplier]  ,[UnitInfo]" +
                                            " ,[UseYears],[InitDate] ,[UsersInfo] ,[SizeInfo]) VALUES ";

                            strUpdateSql += "('" + row["Id"] + "','" + row["BaseObjectId"] + "','" + row["PlanId"] + "','" +
                                                row["InventoryDate"] + "','"
                                                + commintDate + "','" + row["InventoryState"] + "','" +
                                                row["InventoryMemo"] + "','" + row["OldBarcode"] + "','"
                                                + row["InventoryUserInfo"] + "','" + row["AssetTypeInfo"] + "','" +
                                                row["TypeName"] + "','" + row["Barcode"] + "','"
                                                + row["CheckedDate"] + "','" + row["DepartmentInfo"] + "','" +
                                                row["Location"] + "','" + row["Manufacturers"] + "','"
                                                + row["Memo"] + "','" + row["ModelNumber"] + "','" + row["Name"] + "','" + row["NatureInfo"] +
                                                "','" + row["ParentBarcode"] + "','"
                                                + row["SerialNumber"] + "','" + row["ShelfLife"] + "','" + row["SiteInfo"] +
                                                "','" + row["SourceInfo"] + "','"
                                                + row["StartUpDate"] + "','" + row["StateInfo"] + "','" + row["Supplier"] +
                                                "','" + row["UnitInfo"] + "','"
                                                + row["UseYears"] + "','" + row["InitDate"] + "','" + row["UsersInfo"] +
                                                "','" + row["SizeInfo"] + "')";
                        }
                     
                    }
                    else
                    {
                        var strId = row["Id"].ToString();

                        strUpdateSql = "update [AmsInventory_InventoryInfo] set [BaseObjectId]='" + row["BaseObjectId"] + "',[PlanId]='" + row["PlanId"] + "',[InventoryDate]='" + row["InventoryDate"] + "',[CommintDate]='" + commintDate + "',[InventoryState]='" + row["InventoryState"] + "'," +
                       "[InventoryMemo]='" + row["InventoryMemo"] + "',[OldBarcode]='" + row["OldBarcode"] + "',[InventoryUserInfo]='" + row["InventoryUserInfo"] + "',[AssetTypeInfo]='" + row["AssetTypeInfo"] + "',[TypeName]='" + row["TypeName"] + "',[Barcode]='" + row["Barcode"] + "',[CheckedDate]='" + row["CheckedDate"] + "',[DepartmentInfo]='" + row["DepartmentInfo"] + "',[Location]='" + row["Location"] + "'," +
                       "[Manufacturers]='" + row["Manufacturers"] + "',[Memo]='" + row["Memo"] + "',[ModelNumber]='" + row["ModelNumber"] + "',[Name]='" + row["Name"] + "',[NatureInfo]='" + row["NatureInfo"] + "',[ParentBarcode]='" + row["ParentBarcode"] + "',[SerialNumber]='" + row["SerialNumber"] + "',[ShelfLife]='" + row["ShelfLife"] + "',[SiteInfo]='" + row["SiteInfo"] + "',[SourceInfo]='" + row["SourceInfo"] + "'," +
                       "[StartUpDate]='" + row["StartUpDate"] + "',[StateInfo]='" + row["StateInfo"] + "',[Supplier]='" + row["Supplier"] + "',[UnitInfo]='" + row["UnitInfo"] + "',[UseYears]='" + row["UseYears"] + "',[InitDate]='" + row["InitDate"] + "',[UsersInfo]='" + row["UsersInfo"] + "',[SizeInfo] ='" + row["SizeInfo"] + "' where [Id]='" + strId + "'";

                    }
                    if(strUpdateSql.Trim().Length > 0)
                      updateSqlList.Add(strUpdateSql);


                }

            }
            else
            {
                MessageBox.Show("沒有要上傳的數據！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            backgroundWorker1.RunWorkerAsync();//運行backgroundWorker組件
            //顯示進度條窗體
            var form = new ProcessForm(backgroundWorker1, MaxNum);
            form.ShowDialog(this);
            form.Close();

            #region 把上傳后的sdf拷貝回pda

            bool MyPDAExits = pdaFileControl.PDAFileExists(PDASdfFilePath);

            if (MyPDAExits)
            {
                pdaFileControl.DeletePDAFile(PDASdfFilePath);
            }
            //從C盤拷貝數據到PDA
            pdaFileControl.CopyFileToPDA(PCSdfFilePath, PDASdfFilePath);
            #endregion



            Text = "PDA 數據下載/上傳";


            //斷開與 PDA 連接
            pdaFileControl.DisConnectToPDA();
            btnPeekResult.Visible = true;
            QueryData();
        }


        #endregion

        private void btnClose_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void btnPeekResult_Click(object sender, EventArgs e)
        {
            var sb = new StringBuilder();
            sb.Append("總數據：" + updateSqlList.Count + "條\t");
            sb.Append("成功上傳數據：" + factRow + "條\t");
            sb.Append("上傳失敗：" + (updateSqlList.Count - factRow) + "條\n");
            if (m_PlanList.Count > 0)
            {
                for (var i = 0; i < m_PlanList.Count; i++)
                {
                    var strPlanId = m_PlanList[i];

                    var sql = "select b.Name from AmsInventory_InventoryInfo a join AmsInventory_PlanInfo b on a.PlanId = b.Id where a.PlanId='" + strPlanId + "'";
                    var ds = m_Db.GetDataSet(sql);
                    if (ds != null && ds.Tables[0].Rows.Count > 0)
                    {
                        sb.Append("盤點計劃(" + ds.Tables[0].Rows[0][0] + "): ");
                        sql = "select count(*) from AmsInventory_InventoryInfo a join AmsInventory_PlanInfo b on a.PlanId = b.Id where a.PlanId='" + strPlanId + "'";
                        int iTotalCount = m_Db.GetCount(sql);
                        sql = "select count(*) from AmsInventory_InventoryInfo a join AmsInventory_PlanInfo b on a.PlanId = b.Id where a.PlanId='" + strPlanId + "' and InventoryState='Match'";
                        int iMatchCount = m_Db.GetCount(sql);
                        sql =
                            "select count(*) from AmsInventory_InventoryInfo a join AmsInventory_PlanInfo b on a.PlanId = b.Id where a.PlanId='" +
                            strPlanId + "' and InventoryState='Add'";
                        int iAddCount = m_Db.GetCount(sql);
                        sql = "select count(*) from AmsInventory_InventoryInfo a join AmsInventory_PlanInfo b on a.PlanId = b.Id where a.PlanId='" +
                            strPlanId + "' and (InventoryState='Wait' or InventoryState='Lost')";
                        int iLostCount = m_Db.GetCount(sql);
                        sql = "select count(*) from AmsInventory_InventoryInfo a join AmsInventory_PlanInfo b on a.PlanId = b.Id where a.PlanId='" +
                           strPlanId + "' and InventoryState='Updated'";
                        int iUpdatedCount = m_Db.GetCount(sql);
                        sb.Append("總盤點資產:" + iTotalCount + "條; ");
                        sb.Append("匹配資產:" + iMatchCount + "條; ");
                        sb.Append("新增資產:" + iAddCount + "條; ");
                        sb.Append("更新資產:" + iUpdatedCount + "條; ");
                        sb.Append("盤虧資產:" + iLostCount + "條; ");
                        var count = iMatchCount + iAddCount + iUpdatedCount - iTotalCount;
                        if (count > 0)
                        {
                            sb.Append("盤盈資產:" + count + "條\n");
                        }
                        else
                        {
                            sb.Append("\n");
                        }
                    }

                }
            }
            MessageBox.Show(sb.ToString(), "盘点上传提示");

        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            var worker = sender as BackgroundWorker;
            if (IsDataUp) // 上傳
            {
                for (int i = 0; i < updateSqlList.Count; i++)
                {

                    if (m_Db.ExecNoneQuery(updateSqlList[i]) >= 0)
                        factRow += 1;
                    else
                    {

                        var a = updateSqlList[i];
                    }
                    worker.ReportProgress(i);
                    if (worker.CancellationPending) //如果用戶取消則跳出處理數據代碼
                    {
                        e.Cancel = true;
                        break;
                    }
                }
                if (factRow < updateSqlList.Count)
                {
                    MessageBox.Show("數據上傳不完整！上傳失敗" + (updateSqlList.Count - factRow) + "條", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                }
                else
                {
                    int access = 0;
                    for (var j = 0; j < m_PlanList.Count; j++)
                    {
                        access = m_Db.ExecNoneQuery("update AmsInventory_PlanInfo set State ='Finish' where Id='" + m_PlanList[j] + "'");
                         m_SqlSdfDb.ExecNoneQuery("delete from AmsInventory_PlanInfo where Id ='" + m_PlanList[j] + "'");
                         m_SqlSdfDb.ExecNoneQuery("delete from AmsInventory_InventoryInfo where PlanId ='" + m_PlanList[j] + "'");
                    }

                    if (access >= 1)
                        MessageBox.Show("數據上傳完成,上傳成功" + updateSqlList.Count + "條");

                }
               
            }
            else // 下載
            {

                string _DataSource = Application.StartupPath + @"\PDA_AMS_Inventory.db3";
                int query = 0;
                var cn = new SQLiteConnection("Data Source = " + _DataSource + "; ");
                var comm = new SQLiteCommand(cn);//celCon.CreateCommand();
                cn.Open();
                SQLiteTransaction tran = cn.BeginTransaction();
                try
                {
                    var sb = new StringBuilder();
                    for (var i = 0; i < m_SqlList.Count; i++)
                    {
                        sb.Append(m_SqlList[i]);
                        if (i % 100 == 0 || i == (m_SqlList.Count - 1))
                        {

                            query += m_SqlSdfDb.ExecNoneQueryByList(sb, comm);
                            sb = new StringBuilder();
                            worker.ReportProgress(i);
                            if (worker.CancellationPending) //如果用戶取消則跳出處理數據代碼
                            {
                                e.Cancel = true;
                                break;
                            }
                        }
                    }
                    tran.Commit();
                }
                catch(Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
                finally
                {

                    cn.Close();
                }
                


                if (query < m_SqlList.Count)
                {
                    MessageBox.Show("數據下載不完整！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                }
                else
                {
                    var access = 0;
                    foreach (DataGridViewRow dtGvRow in dataGridView1.SelectedRows)
                    {
                        var Row = ((DataRowView)dtGvRow.DataBoundItem).Row;
                        m_PlanId = Row["Id"].ToString();
                        access = m_Db.ExecNoneQuery("update AmsInventory_PlanInfo set State ='Doing' where Id='" + m_PlanId + "'");
                    }
                    if (IsDataUpdate)
                    {
                        if (access >= 1) MessageBox.Show("數據同步成功！");

                    }
                    else
                    {
                       if (access >= 1) MessageBox.Show("數據下載完成！");
                    }
                

                }



            }


        }



        private void backgroundWorker1_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                MessageBox.Show(e.Error.Message);
            }
            else if (e.Cancelled)
            {
            }


        }

        /// <summary>
        /// 同步基础数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLoadBaseData_Click(object sender, EventArgs e)
        {
            IsDataUpdate = true;
            m_SqlList.Clear();


            //檢查C 盤SDF 文件是否存在
            var bSDFExists = false;
            if (File.Exists(PCSdfFilePath))
            {
                bSDFExists = true;
            }

            //存在就刪掉
            if (bSDFExists)
            {
                try
                {
                    File.Delete(PCSdfFilePath);
                }
                catch
                {
                    MessageBox.Show("未清除歷史數據文件，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
            }

            //初始化PDA 文件操作類
            var pdaFileControl = new PDAFileControl();

            //連接到 PDA
            var bPDAConnect = pdaFileControl.ConnectToPDA();

            if (bPDAConnect == false)
            {
                MessageBox.Show("請確認 PDA 已經跟電腦進行同步連接 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }

            //檢查PDA中是否存在SDF 文件 
            var bPDAExits = pdaFileControl.PDAFileExists(PDASdfFilePath);

            if (bPDAExits)
            {
                //從PDA 拷貝SDF文件到C盤
                var bPDACopy = pdaFileControl.CopyFileFromPDA(PDASdfFilePath, PCSdfFilePath);

                if (bPDACopy == false)
                {
                    MessageBox.Show("未創建臨時數據文件，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    pdaFileControl.DisConnectToPDA();
                    return;
                }

            }
            else
            {
                MessageBox.Show("未能在PDA中找到數據庫文件,請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                pdaFileControl.DisConnectToPDA();
                return;
            }

            int access = 0;
            MaxNum = 0;
            access = m_SqlSdfDb.ExecNoneQuery("delete from AmsInventory_BaseDataInfo");
            access = m_SqlSdfDb.ExecNoneQuery("delete from Frame_User");
            var p3 = new SqlParameter("PlanId", m_PlanId);
            var dt3 = m_Db.GetTableRunProc("[BaseDataInfoSelect]", new[] { p3 });
            if (dt3 != null) MaxNum += dt3.Rows.Count;

            if (dt3 != null && dt3.Rows.Count > 0)
            {
                const string strInsertSql3 =
                    "INSERT INTO  [AmsInventory_BaseDataInfo] ([KeyVal] ,[KeyVal2],[KeyVal3] ,[ClassName] ,[TypeName]) VALUES ";

                foreach (DataRow row in dt3.Rows)
                {
                    var strTempSql = strInsertSql3 + "('" + row["KeyVal"] + "','" + row["KeyVal2"] + "','" +
                                      row["KeyVal3"] + "','"
                                      + row["ClassName"] + "','" + row["TypeName"] + "');";

                    m_SqlList.Add(strTempSql);

                }
            }


            var pcUserDt = m_Db.GetDataSet("select * from Frame_User");
            if (pcUserDt != null && pcUserDt.Tables[0].Rows.Count > 0)
            {
                MaxNum += pcUserDt.Tables[0].Rows.Count;
                const string strInsertSql3 =
               "INSERT INTO  [Frame_User] ([Code] ,[Password],[Name] ,[Number] ,[Email],[Mobile],[IsInUsed]) VALUES ";

                foreach (DataRow row in pcUserDt.Tables[0].Rows)
                {
                    var Password = publ.VitDecryptString(row["Password"].ToString());
                    var strTempSql = strInsertSql3 + "('" + row["Code"] + "','" + Password + "','" +
                                      row["Name"] + "','" + row["Number"] + "','" + row["Email"] + "','" +
                                      row["Mobile"] + "','" + row["IsInUsed"] + "');";

                    m_SqlList.Add(strTempSql);

                }

            }




            //將SQL Server 數據庫數據導入C 盤 Sdf 文件操作
            //DownLoadPCDataToPC();

            backgroundWorker1.RunWorkerAsync();//運行backgroundWorker組件
            //顯示進度條窗體
            var form = new ProcessForm(backgroundWorker1, MaxNum);
            form.ShowDialog(this);
            form.Close();


            //檢查C盤文件是否存在
            if (File.Exists(PCSdfFilePath) == false)
            {
                MessageBox.Show("未找到臨時數據文件，未能將數據導入 PDA ，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                pdaFileControl.DisConnectToPDA();
                return;
            }
            //檢查PDA中是否存在SDF 文件 ,存在則刪除, 不用檢查結果,因為拷貝的時候 OverWrite = true
            var MyPDAExits = pdaFileControl.PDAFileExists(PDASdfFilePath);

            if (MyPDAExits)
            {
                pdaFileControl.DeletePDAFile(PDASdfFilePath);
            }


            //從C盤拷貝數據到PDA
            var MyPDACopy = pdaFileControl.CopyFileToPDA(PCSdfFilePath, PDASdfFilePath);

            if (MyPDACopy == false)
            {

                MessageBox.Show("數據同步失敗，請重試 ！！！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            pdaFileControl.DisConnectToPDA();

        }




    }
}
